# -*- coding: utf-8 -*-
__author__ = 'gongqf'
import urllib, urllib2, re, sqlite3, cookielib, json, smtplib, os
from email.Message import Message

DB_FILE = os.path.join('data','car.sqlite')
QUERY_URL = 'http://wf.nbjj.gov.cn/wzquery.jsp'
QUERY_REFERER = 'http://wf.nbjj.gov.cn/'
SMTP_SERVER = 'mail.ndtv.com.cn'
SMTP_USER = 'gongqf@ndtv.com.cn'
SMTP_PASS = '118103'
SMTP_PORT = '25'
ILLEGAL_PART_NUMBER = 8

FETION_LOGIN = 'http://f.10086.cn/im5/login/loginHtml5.action'
FETION_QUERYFRIENDS = 'http://f.10086.cn/im5/index/queryFriends.action'
FETION_ADDFRIEND = 'http://f.10086.cn/im5/user/addFriendSubmit.action'
FETION_SENDMSG = 'http://f.10086.cn/im5/chat/sendNewMsg.action'
FETION_SENDSMG = 'http://f.10086.cn/im5/chat/sendNewShortMsg.action'
FETION_LOGOUT = 'http://f.10086.cn/im5/index/logoutsubmit.action'
FETION_USER = '13957861878'
FETION_PASS = '33g02g04'
FETION_UID = '316152686'

def get_str(v):
    if v:
        return v
    else:
        return '无'


def query_car_from_database():
    conn = sqlite3.connect(DB_FILE)
    conn.text_factory = str
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    rs = cursor.execute(
        "SELECT carid,carlpn,substr(carvin,-6,6) as carvin,carclassid,carclassname,caremail,carowner,carmobile,carfetionid FROM car,carclass where car.carclass=carclass.carclassid").fetchall()
    cursor.close()
    conn.close()
    return rs


def update_car_fetionid(carid, fetionid):
    conn = sqlite3.connect(DB_FILE)
    conn.text_factory = str
    conn.execute('UPDATE CAR SET carfetionid=? WHERE carid=?', (fetionid, carid))
    conn.commit()
    conn.close()


def query_car_illegal_from_database(carid):
    conn = sqlite3.connect(DB_FILE)
    conn.text_factory = str
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    rs = []
    [rs.append(x[0]) for x in cursor.execute("SELECT illegaltime FROM illegal WHERE carid=?", (carid,)).fetchall()]
    cursor.close()
    conn.close()
    return rs


def insert_new_illegal(r):
    conn = sqlite3.connect(DB_FILE)
    conn.text_factory = str
    for c in r:
        if c['illegal']:
            for i in c['illegal']:
                conn.execute('INSERT INTO illegal(' + ','.join(i.keys()) + ') VALUES(' + ','.join(['?'] * len(i)) + ')',
                    tuple(i.values()))
                # conn.execute("INSERT INTO illegal(carid,illegaltime,illegallocation,illegalaction,illegalpoint,illegalfine,illegaloffice) VALUES(?,?,?,?,?,?,?)", x)
    conn.commit()
    conn.close()


def send_fetion(r):
    def get_fetion_uid(friends, mobile):
        print u'通过移动手机号获得飞信用户号', mobile
        for f in friends:
            if f['relationStatus'] and mobile == f['mobileNo']:
                return f['idContact']

    def send_msg(uid, msg):
        print u'发送飞信消息', uid, msg.decode('utf-8')
        send_req = urllib2.Request(FETION_SENDMSG, urllib.urlencode({'touserid': uid, 'msg': msg}))
        send_res = urllib2.urlopen(send_req).read()
        if send_res == '{"info":消息发送成功!}':
            return True

    def send_smg(uid, msg):
        print u'发送手机短消息', uid, msg.decode('utf-8')
        send_req = urllib2.Request(FETION_SENDSMG, urllib.urlencode({'touserid': uid, 'msg': msg}))
        send_res = urllib2.urlopen(send_req).read()
        if send_res == '{"info":消息发送成功!}':
            return True

    def get_fetion_friends():
        print u'获取飞信好友列表'
        friends_req = urllib2.Request(FETION_QUERYFRIENDS)
        friends_res = urllib2.urlopen(friends_req).read()
        friends = json.loads(friends_res)
        return friends['contacts']

    def add_fetion_friend(mobile):
        print u'增加飞信好友', mobile
        add_req = urllib2.Request(FETION_ADDFRIEND, urllib.urlencode({'number': mobile, 'type': '0'}))
        add_res = urllib2.urlopen(add_req).read()
        return add_res

    def login_fetion(u, p):
        print u'开始登录飞信'
        login_req = urllib2.Request(FETION_LOGIN, urllib.urlencode({'m': u, 'pass': p}))
        login_res = urllib2.urlopen(login_req).read()
        t = re.compile(r't=(\d+)').findall(login_res)[0]
        return t

    def logout_fetion(t):
        print u'退出飞信'
        logout_req = urllib2.Request(FETION_LOGOUT, urllib.urlencode({'t': t}))
        logout_res = urllib2.urlopen(logout_req).read()
        return logout_res


    opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cookielib.CookieJar()))
    urllib2.install_opener(opener)
    t = login_fetion(FETION_USER, FETION_PASS)

    for c in r:
        car = c['car']
        illegal = c['illegal']
        uid = car['carfetionid']
        msg = car['carowner'] + '，'
        if illegal:
            msg += '你的 ' + car['carlpn'] + ' 车辆有' + str(len(illegal)) + '条新的违章记录。'
            for i, m in enumerate(illegal):
                msg += '第' + str((i + 1)) + '条：' + m['illegaltime'] + ' 在 ' + m['illegallocation'] + '，因 ' + m[
                                                                                                             'illegalaction'] + ' 被记' + str(
                    m['illegalpoint']) + '分，罚款' + str(m['illegalfine']) + '元，发现机关为 ' + m['illegaloffice'] + '。 '
        else:
            msg += '你登记的 ' + car['carlpn'] + ' 车辆信息有误，请核对下列数据：车架号是 ' + get_str(car['carvin']) + '，车辆类型是 ' + car[
                                                                                                            'carclassname']
        if uid:
#            pass
            send_msg(uid, msg)

        #        验证中国移动手机号码正则表达式 2012/1/10
        #        134-139 150-152 158 159 182 187 188#
        #        pattern = r'^1((3[4-9])||(5[0-2])||(5[8-9])||(8[8-9])||(82))\d{8}$'

        elif re.match(r'^1((3[4-9])||(5[0-2])||(5[8-9])||(8[8-9])||(82))\d{8}$', car['carmobile']):
        #            获取好友列表
            friends = get_fetion_friends()
            #            获取飞信的用户号
            fid = get_fetion_uid(friends, car['carmobile'])
            for x in friends:
                print x['relationStatus'], x['idContact'], x['mobileNo'], x['localName']
            if fid:
                update_car_fetionid(car['carid'], fid)
            else:
                add_fetion_friend(car['carmobile'])
            #        发给自己的短信
        send_smg(FETION_UID, msg)

    logout_fetion(t)


def send_email(r):
    def connect():
    #        "connect to smtp server and return a smtplib.SMTP instance object"
        server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
        server.ehlo()
        server.login(SMTP_USER, SMTP_PASS)
        return server

    def sendmessage(server, to, subj, content):
    #        "using server send a email"
        msg = Message()
        msg['MIME-Version'] = '1.0'
        msg['Content-type'] = 'text/html'
        msg['From'] = SMTP_USER
        msg['To'] = to
        msg['Subject'] = subj
        #        curr datetime, rfc2822
        #        msg['Date'] = email.Utils.formatdate()
        msg.set_payload(content)
        try:
        #        may also raise exc
            server.sendmail(SMTP_USER, to, str(msg))
        except Exception, ex:
            print Exception, ex
            print 'Error - send failed'
        else:
            print "send success!"

    def footer():
        return '<div align="center">Powered by ' + __author__ + ',Thx.</div>'

    def gen_illegal_html(illegal):
        content = '\
            <table align="center" width="98%" border="1" cellpadding="4" cellspacing="0">\
                <tr align="center">\
                    <th>车牌号码</th>\
                    <th>车辆类型</th>\
                    <th>违法时间</th>\
                    <th>违法地点</th>\
                    <th>违法行为</th>\
                    <th>记分</th>\
                    <th>罚款金额</th>\
                    <th>发现机关</th>\
                </tr>'
        for i in illegal:
            content = content + '\
                <tr>\
                    <td align="center">' + car['carlpn'] + '</td>\
                    <td align="center">' + car['carclassname'] + '</td>\
                    <td align="center">' + i['illegaltime'] + '</td>\
                    <td>' + i['illegallocation'] + '</td>\
                    <td>' + i['illegalaction'] + '</td>\
                    <td>' + str(i['illegalpoint']) + '分</td>\
                    <td>' + str(i['illegalfine']) + '元</td>\
                    <td>' + i['illegaloffice'] + '</td>\
                </tr>'
        content += '\
            </table>'
        return content

    def gen_car_html(car):
        content = '\
            <table align="center" width="98%" border="1" cellpadding="4" cellspacing="0">\
                <tr align="center">\
                    <th>车牌号码</th>\
                    <th>车架号码</th>\
                    <th>车辆类型</th>\
                    <th>Email</th>\
                    <th>车主</th>\
                    <th>移动手机</th>\
                </tr>\
                <tr align="center">\
                    <td>' + car['carlpn'] + '</td>\
                    <td>' + get_str(car['carvin']) + '</td>\
                    <td>' + car['carclassname'] + '</td>\
                    <td>' + get_str(car['caremail']) + '</td>\
                    <td>' + car['carowner'] + '</td>\
                    <td>' + get_str(car['carmobile']) + '</td>\
                </tr>\
            </table>'
        return content

    server = connect()
    for c in r:
        car = c['car']
        illegal = c['illegal']
        to = car['caremail']
        subj = car['carowner'] + '，'
        if illegal:
            subj += '你有' + str(len(illegal)) + '条新的车辆违章记录'
            #    print 'Type message text, end with line="."'
            text = gen_illegal_html(illegal)
        else:
            subj += '你登记的车辆信息有误'
            text = gen_car_html(car)
        text += footer()

#        pattern=^(\w)+(\.\w+)*@(\w)+((\.\w{2,4}){1,3})$
        if re.match(r'^(\w)+(\.\w+)*@(\w)+((\.\w+)+)$', to):
            sendmessage(server, to, subj.decode('utf-8').encode('gbk'), text.decode('utf-8').encode('gbk'))
#            pass
#        发给自己的邮箱
        sendmessage(server, SMTP_USER, subj.decode('utf-8').encode('gbk'), text.decode('utf-8').encode('gbk'))


def query_car_illegal(car):
    print u'车辆登记信息', car
    carid = car['carid']
    carlpn = car['carlpn']
    carvin = car['carvin']
    carclassid = car['carclassid']
    #    发送要查询信息
    #    车牌号两种格式都可以 浙B·9Y829 浙B9Y829
    #    'cheHaoMa' : '浙B·9Y829',
    #    'clsbdm' : '056071',
    #    'cheHaoMa' : '浙B·8042T',
    #    'clsbdm' : '489575',

    query_post_data = urllib.urlencode({
        'cheHaoMa': carlpn,
        'clsbdm': carvin,
        'cheClass': carclassid
    })
    #    查询的地址
    query_request = urllib2.Request(
        url = QUERY_URL,
        data = query_post_data
    )
    query_request.add_header('Referer', QUERY_REFERER)

    #    返回查询的结果
    query_result = urllib2.urlopen(query_request).read()
    #    print query_result
    #    道路交通违法记录条数是：0 条
    pattern = u'道路交通违法记录条数是：(\d+) 条'.encode('utf-8')
    search_records = re.findall(pattern, query_result)
    print u'道路交通违法记录条数是：', search_records
#    判断是否查询参数正确，正确返回[0]或[1]，错误返回[]
    if search_records:
#        判断查询到的条数是不是大于0
        if search_records[0]:
            pattern = r'<table width="98%" border="1" cellpadding="4" cellspacing="0"  >.+?</table>'
            illegal_content = re.search(pattern, query_result, re.S).group(0)
            pattern = r'<div.+?</div>|<!--.+?-->'
            illegal_content = re.sub(pattern, '', illegal_content, flags=re.S)
            pattern = r'<td.+?</td>'
            illegal_tds = re.findall(pattern, illegal_content)
            illegal_recs = []
            if illegal_tds:
                illegal_parts = []
                for x in illegal_tds:
                    pattern = r'<.+?>|&nbsp;'
                    x = re.sub(pattern, '', x)
                    print x.decode('utf-8')
                    illegal_parts.append(x)
                illegal_len = len(illegal_parts) // ILLEGAL_PART_NUMBER
                illegal_pos = (illegal_len - 1) * ILLEGAL_PART_NUMBER
                has_illegal = query_car_illegal_from_database(carid)
                print u'老的违章记录', has_illegal
                for x in xrange(illegal_len):
                    illegaltime = illegal_parts[illegal_pos + 2][:19]
                    if illegaltime not in has_illegal:
                        illegallocation = illegal_parts[illegal_pos + 3]
                        illegalaction = illegal_parts[illegal_pos + 4]
                        illegalpoint = int(re.search(r'\d+', illegal_parts[illegal_pos + 5]).group(0))
                        illegalfine = int(re.search(r'\d+', illegal_parts[illegal_pos + 6]).group(0))
                        illegaloffice = illegal_parts[illegal_pos + 7]
                        illegal_recs.append((
                            carid, illegaltime, illegallocation, illegalaction, illegalpoint, illegalfine,
                            illegaloffice))
                print u'新的违章记录', illegal_recs
            return illegal_recs


if __name__ == "__main__":
    car = query_car_from_database()
    r = []
    for c in car:
    #        返回 None 或 [] 或 [[..]...]
    #        None 表示查询错误
    #        [] 表示没有违章记录或没有新的违章记录
        illegal = query_car_illegal(c)

        if illegal:
        # 有新的违章记录，转换成dict
            i = [dict(zip([
                'carid', 'illegaltime', 'illegallocation', 'illegalaction', 'illegalpoint', 'illegalfine',
                'illegaloffice'], row)) for row in illegal]
            r.append({'car': c, 'illegal': i})
        elif illegal is None:
        # 登记的信息有误
            r.append({'car': c, 'illegal': None})
    print r
    if r:
        insert_new_illegal(r)
        send_email(r)
        send_fetion(r)
